/***********************************************************************************************************************************
 *    Name: Jobs_Stalled.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: 
**/
USE msdb;
DECLARE @enum_jobs TABLE (
	job_id					UNIQUEIDENTIFIER	NOT Null,
	last_run_date			INT					NOT Null,
	last_run_time			INT					NOT Null,
	next_run_date			INT					NOT Null,
	next_run_time			INT					NOT Null,
	next_run_schedule_id	INT					NOT Null,
	requested_to_run		BIT					NOT Null,
	request_source			INT					NOT Null,
	request_source_id		SYSNAME					Null,
	running					BIT					NOT Null,
	current_step			INT					NOT Null,
	current_retry_attempt	INT					NOT Null,
	job_state				INT					NOT Null);
INSERT INTO @enum_jobs EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, 'sa';

SELECT sj.name, ja.start_execution_date, ej.running, ej.current_step, ej.current_retry_attempt
  FROM @enum_jobs ej
	INNER JOIN dbo.sysjobactivity ja ON (ej.job_id = ja.job_id)
	INNER JOIN dbo.sysjobs sj ON (ej.job_id = sj.job_id)
  WHERE 0 < current_retry_attempt;
